import dbpopulator.DBTupleGenerator;

import java.io.*;
import java.nio.*;
import java.util.*;

public class SampleData {

	/**
	 * The tuple generator
	 */
	private DBTupleGenerator tupleGenerator = new DBTupleGenerator();
	
	/**
	 * The tables from the schema
	 */
	private List<Table> tables = new ArrayList<Table>();
	
	/**
	 * The sample tuple data
	 */
	private Comparable[][][] resultTest = null;

	/**
	 * Creates a SampleData object with the correct schema 
	 * @param n tuples size of each table
	 */
	public SampleData(int n) {
		
		/**
		 * Add the Student table schema
		 */
		tables.add(new Table(
    			"Student", 
    			new String [] {"Id", "Name", "Address", "Status"}, 
    			new String [] {"Integer", "String", "String", "String"}, 
    			new String [] {"Id"}, 
    			null
    			));
		
		/**
		 * Add the Professor table schema
		 */
    	tables.add(new Table(
    			"Professor", 
    			new String [] {"Id", "Name", "DeptId"}, 
    			new String [] {"Integer","String","String","String"}, 
    			new String [] {"Id"}, 
    			null
    			));
    	
    	/**
    	 * Add the Course table schema
    	 */
    	tables.add(new Table(
    			"Course",
    			new String [] {"CrsCode", "DeptId", "CrsName", "Descr"},
    			new String [] {"String", "String", "String", "String"},
    			new String [] {"CrsCode"},
    			null
    			));
    	
    	/**
    	 * Add the Transcript table schema
    	 */
    	tables.add(new Table(
    			"Transcript",
    			new String [] {"StudId", "CrsCode", "Semester", "Grade"},
    			new String [] {"Integer", "String", "String", "String"},
    			new String [] {"StudId", "CrsCode", "Semester"},
    			new String [][] {{"StudId", "Student", "Id"}, {"CrsCode", "Course", "CrsCode"}}
    			));
    	
    	/**
    	 * Add the Teaching table schema
    	 */
    	tables.add(new Table(
    			"Teaching",
    			new String [] {"ProfId", "CrsCode", "Semester"},
    			new String [] {"String", "String", "String"},
    			new String [] {"CrsCode", "Semester"},
    			new String [][] {{"CrsCode", "Course", "CrsCode"}, {"CrsCode Semester", "Transcript", "CrsCode Semester"}}
    			));
    	
    	/**
    	 * Add the relational schema to the tuple generator.
    	 */
    	for (Table t : tables) {
    		tupleGenerator.addRelSchema(t.name, t.attr, t.dom, t.pk, t.fk);
    	}
    	
    	/**
    	 * Create the sample data
    	 */
    	this.resultTest = tupleGenerator.generate(tables.size(), n);
    	
	}
	
	public void printMySQL() {
		this.printMySQL(System.out);
	}
	
	public void printMySQL(PrintStream out) {
		
for ( int i = 0; i < resultTest.length; i++) {
			
			out.println();
			out.println("--");
			out.println("-- MySQL Schema and Dummy Data");
			out.println("--");
			
			for (Table t : this.tables) {
				
				out.println();
				out.println("--");
				out.println("-- Table structure for table `" + t.name + "`");
				out.println("--");
				out.println("CREATE TABLE IF NOT EXISTS `" + t.name + "` (");
				
				for (int attri = 0; attri < t.attr.length; attri++) {
					if (t.dom[attri].toUpperCase().compareTo("STRING") == 0) {
						out.println("  `" + t.attr[attri] + "` " + "VARCHAR(255) " + ((attri != t.attr.length - 1) ? ", " : ((t.pk != null) ? ", ": " ")));
					} else {
						out.println("  `" + t.attr[attri] + "` " + (t.dom[attri]).toUpperCase() + ((attri != t.attr.length - 1) ? ", " : ((t.pk != null) ? ", ": " ")));
					}
				}
				
				if (t.pk != null) {
					out.print("  PRIMARY KEY ( ");
					for (int pki = 0; pki < t.pk.length; pki++){
						out.print("`" + t.pk[pki] + ((pki != t.pk.length - 1) ? "`, " : "` "));
					}
					out.println(")" + ((t.fk != null) ? ", " : " "));
				}
				
				if (t.fk != null) {
					for (int fki = 0; fki < t.fk.length; fki++){
						out.println("  FOREIGN KEY ( `"+ t.fk[fki][0].replaceAll(" ", "`, `") + "` ) REFERENCES `" + t.fk[fki][1] + "` ( `" + t.fk[fki][2].replaceAll(" ", "`, `") + "` )" + ((fki != t.fk.length - 1) ? ", " : " "));
					}
				}
			
				out.println(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
			}
			
			out.println();
			out.println("--");
			out.println("-- Dummy data for table `" + tables.get(i).name + "`");
			out.println("--");
			
			for (int j = 0; j < resultTest[i].length; j++) {
			
				out.print("INSERT INTO `" + tables.get(i).name + "` ( ");
				
				for (int attri = 0; attri < tables.get(i).attr.length; attri++) {
					out.print("`" + tables.get(i).attr[attri] + ((attri != tables.get(i).attr.length - 1) ? "`, ": "` "));
				}
			
				out.print(") VALUES ( ");
				
				for (int k = 0; k < resultTest[i][j].length; k++) {				
					out.print("`" + resultTest[i][j][k] + ((k != resultTest[i][j].length - 1) ? "`, " : "` "));
				}
				
				out.println(");");

			}
			
		}
		
	}
	
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		/**
		 * Create some sample data
		 */
		SampleData sample = new SampleData(5);
		
		/**
		 * Generate MySQL Schema and Sample Data
		 */
		try {
			FileOutputStream fos = new FileOutputStream(new File("mysql/mysql.sql"));
			sample.printMySQL(new PrintStream(fos));
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
	}

}
